# -*- encoding:utf-8 -*-
# @Date  :2018-10-31
# @Author:田国新（tianguoxin#antiy.cn）

# 角色同步

import json
from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor
from config import config
from lib.validform import V, ValidateForm
from lib.funcs import validateAnguan

app = Blueprint(__name__ + "_app", __name__)


@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 新增机构
@app.route('/api/sync/mechanism/add', methods=['POST'])
def add_mechanism():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            node_id=['机构ID', V.required()],
            node_name=['机构名称', V.required(), V.maxlength(100)],
            parent_node_id=['上级单位ID', V.required()],
            trade_id=['单位类型', V.required(), V.maxlength(50)],
            belong_area=['所属区域', V.required()],  # 东北西北
            province=['省份', V.optional()],
            city=['城市', V.optional()],
            area=['区/县', V.optional()],
            linkman=['联系人', V.optional(), V.maxlength(20)],
            supervision_node_id=['监管关系ID', V.required()],
            telephone=['联系电话', V.optional(), V.pattern('^1[0-9]{10}$')],
            email=['联系邮箱', V.optional(), V.email()],
            remarks=['备注', V.optional(), V.maxlength(512)],
            r_person=['当前用户', V.required(), V.maxlength(30)])
        (flag, data) = form.validate()
        if not flag: 
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        g.cursor.execute(
            """select agency_name from sys_agency_info where agency_name =%(node_name)s and state = '1'""",
            (data))
        agency_name = g.cursor.fetchone()
        g.cursor.execute(
            """select agency_id from sys_agency_info where agency_id =%(parent_node_id)s and state = '1'""",
            (data))
        parent_agency_id = g.cursor.fetchone()
        g.cursor.execute(
            """select agency_id from sys_agency_info where agency_id =%(supervision_node_id)s and state = '1'""",
            (data))
        supervision_agency_id = g.cursor.fetchone()
        g.cursor.execute(
            """select type_id from h_dept_type_info where type_id =%(trade_id)s""",
            (data))
        type_id = g.cursor.fetchone()
        if agency_name is not None:
            return json.dumps({
                'status': 'E2',
                'msg': {
                    'errorKey': 'agency_name',
                    'message ': '重复'
                }
            })
        if parent_agency_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'parent_agency_id',
                    'message ': '不存在'
                }
            })
        if supervision_agency_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'supervision_agency_id',
                    'message ': '不存在'
                }
            })
        if type_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'trade_id',
                    'message ': '不存在'
                }
            })
        g.cursor.execute(
            """ insert into sys_agency_info
                (node_type_id,agency_name,type_id,agency_id,parent_agency_id,belong_area,province,city,area,linkman,remarks,state,telephone,linkman_email,r_person,supervision_agency_id,data_source,update_time)
                values(1,%(node_name)s,%(trade_id)s,%(node_id)s,%(parent_node_id)s,
                %(belong_area)s,%(province)s,%(city)s,%(area)s,%(linkman)s,%(remarks)s,'1',%(telephone)s,%(email)s,(select person_name from sys_person where state='1' and person_id = %(r_person)s),%(supervision_node_id)s,'0',now())""", (data))
        g.cursor.execute("""INSERT into h_agency_synchroniz_info
                            (agency_id,agency_name_change,agency_type_change,parent_node_change,supervision_node_change,province_change,city_change,area_change,person_change,email_change,phoneno_change)
                            values(%(node_id)s,%(node_name)s, (select type_name from h_dept_type_info where type_id =%(trade_id)s),(select agency_name from sys_agency_info where agency_id = %(parent_node_id)s),
                                    ((select agency_name from sys_agency_info where agency_id = %(supervision_node_id)s)),%(province)s,%(city)s,%(area)s,%(linkman)s,%(email)s,%(telephone)s)""", (data))
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception,e:
        return json.dumps({
            "status": "E0",
            'msg': '新增机构失败',
            "warn": str(e)
        })


# 修改机构
@app.route('/api/sync/mechanism/update', methods=['POST'])
def update_mechanism():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            node_id=['机构ID', V.required()],
            node_name=['机构名称', V.required(), V.maxlength(100)],
            parent_node_id=['上级单位ID', V.required()],
            trade_id=['单位类型', V.required(), V.maxlength(50)],
            belong_area=['所属区域', V.required()],  # 东北西北
            province=['省份', V.optional()],
            city=['城市', V.optional()],
            area=['区/县', V.optional()],
            linkman=['联系人', V.optional(), V.maxlength(20)],
            supervision_node_id=['监管关系ID', V.required()],
            telephone=['联系电话', V.optional(), V.pattern('^1[0-9]{10}$')],
            email=['联系邮箱', V.optional(), V.email()],
            remarks=['备注', V.optional(), V.maxlength(512)],
            r_person=['当前用户', V.required(), V.maxlength(30)])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        g.cursor.execute(
            """select agency_name from sys_agency_info where agency_name =%(node_name)s and state = '1' and agency_id != %(node_id)s""",
            (data))
        agency_name = g.cursor.fetchone()
        g.cursor.execute(
            """select agency_id from sys_agency_info where agency_id =%(node_id)s and state = '1'""",
            (data))
        agency_id = g.cursor.fetchone()
        g.cursor.execute(
            """select agency_id from sys_agency_info where agency_id =%(parent_node_id)s and state = '1'""",
            (data))
        parent_agency_id = g.cursor.fetchone()
        g.cursor.execute(
            """select agency_id from sys_agency_info where agency_id =%(supervision_node_id)s and state = '1'""",
            (data))
        supervision_agency_id = g.cursor.fetchone()
        g.cursor.execute(
            """select type_id from h_dept_type_info where type_id =%(trade_id)s""",
            (data))
        type_id = g.cursor.fetchone()
        if agency_name is not None:
            return json.dumps({
                'status': 'E2',
                'msg': {
                    'errorKey': 'agency_name',
                    'message ': '重复'
                }
            })
        if parent_agency_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'parent_agency_id',
                    'message ': '不存在'
                }
            })
        if supervision_agency_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'supervision_agency_id',
                    'message ': '不存在'
                }
            })
        if type_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'trade_id',
                    'message ': '不存在'
                }
            })
        if agency_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'agency_id',
                    'message ': '不存在'
                }
            })
        g.cursor.execute(
            """ update sys_agency_info set agency_name = %(node_name)s,r_person=%(r_person)s,type_id = %(trade_id)s,
                        parent_agency_id = %(parent_node_id)s,belong_area= %(belong_area)s,
                        province = %(province)s,city = %(city)s,area = %(area)s,linkman = %(linkman)s,
                        remarks = %(remarks)s,telephone = %(telephone)s,linkman_email = %(email)s,supervision_agency_id =  %(supervision_node_id)s,
                        update_time = now()
                        where agency_id = %(node_id)s """, (data))
        g.cursor.execute("""INSERT into h_agency_synchroniz_info
                            (agency_id,agency_name_change,agency_type_change,parent_node_change,supervision_node_change,province_change,city_change,area_change,person_change,email_change,phoneno_change)
                            values(%(node_id)s,%(node_name)s,(select type_name from h_dept_type_info where type_id =%(trade_id)s),(select agency_name from sys_agency_info where agency_id = %(parent_node_id)s),
                                    ((select agency_name from sys_agency_info where agency_id = %(supervision_node_id)s)),%(province)s,%(city)s,%(area)s,%(linkman)s,%(email)s,%(telephone)s)""",(data))
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception:
        return json.dumps({
            "status": "E0",
            'msg': '修改机构失败',
        })


# 删除节点信息
@app.route('/api/sync/mechanism/delete', methods=['POST'])
def delete_mechanism():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            node_id=['机构编号', V.required()],
            r_person=['登记人', V.required(), V.maxlength(30)])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E2'
            data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
            return json.dumps(data)
        g.cursor.execute(
            """ select agency_id from sys_agency_info where agency_id = %(node_id)s and state = '1'""",
            (data))
        agency_id = g.cursor.fetchone()
        if agency_id is None:
            return json.dumps({
                'status': 'E3',
                'msg': {
                    'errorKey': 'agency_id',
                    'message ': '不存在'
                }
            })
        g.cursor.execute(
            """ with RECURSIVE cte as
            (select a.agency_id,a.agency_name,a.parent_agency_id,a.state,a.node_type_id from sys_agency_info a where agency_id = %(node_id)s
            union all
            select k.agency_id,k.agency_name,k.parent_agency_id,k.state,k.node_type_id from sys_agency_info k inner join cte c on c.agency_id= k.parent_agency_id
            )select agency_id,agency_name,state from cte where cte.node_type_id = '1'and cte.state = '1'""", (data))
        row = g.cursor.fetchall()
        g.cursor.execute(
            """
            with RECURSIVE cte as
            (select a.agency_id,a.agency_name,a.supervision_agency_id,a.state,a.node_type_id from sys_agency_info a where agency_id = %(node_id)s
            union all
            select k.agency_id,k.agency_name,k.supervision_agency_id,k.state,k.node_type_id from sys_agency_info k inner join cte c on c.agency_id= k.supervision_agency_id
            )select agency_id,agency_name,state from cte where cte.node_type_id = '1'and cte.state = '1'
            """, (data))
        line = g.cursor.fetchall()
        g.cursor.execute(
            """
               select agency_id from h_information_system where agency_id = %(node_id)s
            """, (data))
        system = g.cursor.fetchall()
        g.cursor.execute("""
            select agency_id from h_system_node where agency_id = %(node_id)s
        """, (data))
        node = g.cursor.fetchall()
        # if len(row) > 1 or len(system) >= 1 or len(line) > 1 or len(node) > 1:
        #     return json.dumps({"status": "E1", 'msg': "此部门有下属机构或信息系统或系统节点或有监管机构不可以删除"})
        g.cursor.execute("""select * from sys_person where agency_id = %(node_id)s and state = '1'""", (data))
        person = g.cursor.fetchall()
        if len(row) > 1:
            return json.dumps({"status": "E1", 'msg': "此部门有下属机构不可以删除"})
        elif len(line) > 1:
            return json.dumps({"status": "E1", 'msg': "此部门有监管机构不可以删除"})
        elif len(system) > 0:
            return json.dumps({"status": "E1", 'msg': "此部门有系统不可以删除"})
        elif len(node) > 0:
            return json.dumps({"status": "E1", 'msg': "此部门有设备不可以删除"})
        elif len(person) > 0:
            return json.dumps({"status": "E1", 'msg': "此部门有用户不可以删除"})
        else:
            g.cursor.execute(
                """update sys_agency_info set state = '0' ,update_time = now() ,r_person = (select person_name from sys_person where person_id = %(r_person)s) where agency_id = %(node_id)s """,
                (data))
            g.cursor.execute("""INSERT into h_agency_synchroniz_info
             (agency_id,agency_name_change,agency_type_change,parent_node_change,supervision_node_change,province_change,
                                city_change,area_change,person_change,email_change,phoneno_change)
                            select a.agency_id,a.agency_name as agency_name_change,b.type_name as agency_type_change,
                            c.parent_node_change,d.supervision_node_change,a.province as province_change,a.city as city_change,
                            a.area as area_change,a.linkman as person_change,a.linkman_email as email_change,a.telephone as phoneno_change
                            from sys_agency_info a
                            left join h_dept_type_info b on a.type_id = b.type_id
                            left join (select a.agency_name as parent_node_change,b.agency_id from sys_agency_info a
                                        left join (select parent_agency_id,agency_id from sys_agency_info ) b on a.agency_id = b.parent_agency_id
                                        where b.agency_id = %(node_id)s) c on a.agency_id = c.agency_id
                            left join (select a.agency_name as supervision_node_change,b.agency_id from sys_agency_info a
                                        left join (select supervision_agency_id,agency_id from sys_agency_info) b on a.agency_id = b.supervision_agency_id
                                        where b.agency_id = %(node_id)s) d on a.agency_id = d.agency_id
                            where a.agency_id = %(node_id)s
                            """, (data))
            g.conn.commit()
            return json.dumps({"status": "S0"})
    except Exception, e:
        return json.dumps({
            "status": "E0",
            'msg': '删除机构失败',
            "warn": str(e)
        })